package edu.nf.db.util;

import java.sql.*;

/**
 * @Author lzp
 * @Date 2024-05-08
 * sql 执行器,核心入口类
 */
public class SqlExecutor {

    /**
     * 用于与数据库建立连接
     */
    private final Connection connection;

    /**
     * 构造方法传入一个 Connection对象
     * @param connection
     */
    public SqlExecutor(Connection connection) {
        this.connection = connection;
    }

    /**
     * 执行增删改操作
     * @param sql sql语句
     * @param params sql语句所需要的参数
     * @return 受影响行数
     */
    public int executeUpdate(String sql,Object... params) {
        if (connection == null){
            throw new RuntimeException("null connection");
        }
        //isEmpty() 判断sql的内容是否为空
        if (sql == null || sql.isEmpty()){
            throw new RuntimeException("null SQL statement");
        }
        //声明PreparedStatement
        PreparedStatement ps= null;
        try {

            //预编译发送sql并返回ps对象
            ps = connection.prepareStatement(sql);
            //设置参数
            setParmeters(ps,params);
            //执行sql返回受影响的行数
            return ps.executeUpdate();

        }catch (SQLException e){
            //异常重抛
            throw new RuntimeException("执行失败 execute fail",e);
        }finally {
            //关闭资源
            close(ps);
            close();
        }

    }

    /**
     * 批量操作
     * @param sql sql语句
     * @param params 批量参数
     * @return 返回多条影响的行数
     */
    public int[] executeBatch(String sql,Object[][] params){
        if (connection == null){
            throw new RuntimeException("null connection");
        }
        if (sql == null || sql.isEmpty()){
            throw new RuntimeException("null SQL statement");
        }
        PreparedStatement ps= null;
        try {
            ps = connection.prepareStatement(sql);
            //批量设置参数
            for (Object[] param : params){
                setParmeters(ps,param);
                //将PS的操作缓存到内存中，最后再批量提交到数据库中
                ps.addBatch();
            }
            //批量执行
            return ps.executeBatch();
        }catch (SQLException e){
            throw new RuntimeException("",e);
        }finally {
            close(ps);
            close();
        }

    }

    /**
     * 查询
     * @param sql sql语句
     * @param handler 结果集处理
     * @param params 查询参数
     * @return 查询结果
     */
    public <T> T executeQuery(String sql,ResultSetHandler<T> handler,
                              Object... params){
        if (connection == null){
            throw new  RuntimeException("null connection");
        }
        if (sql == null || sql.isEmpty()){
            throw new RuntimeException("null SQL statement");
        }
        if (handler == null){
            throw new RuntimeException("null ResultSetHandler");
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        //返回的查询数据
        T result = null;
        try{
            ps = connection.prepareStatement(sql);
            //设置参数
            setParmeters(ps,params);
            //
            rs = ps.executeQuery();
            //执行查询并返回结果集
            result = handler.handle(rs);
            return result;
        }catch (SQLException e){
            throw new RuntimeException("",e);
        }finally {
            close(rs);
            close(ps);
            close();
        }
    }


    /**
     * 设置sql的参数
     * @param ps PreparedStatement 对象
     * @param params 参数集合
     * @throws SQLException SQL异常
     */
    private  void setParmeters(PreparedStatement ps,Object... params)throws SQLException{
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i+1,params[i]);
        }
    }

    /**
     *关闭结果集对象ResultSet
     */
    private void close(ResultSet res){
        if (res != null){
            try {
                res.close();
            } catch (SQLException e) {
                throw new RuntimeException("Close ResultSet fail",e);
            }
        }
    }

    /**
     *关闭Statement
     */
    private void close(Statement st){
        if (st!= null){
            try {
                st.close();
            } catch (SQLException e) {
                throw new RuntimeException("Close Statement fail",e);
            }
        }
    }

    /**
     * 关闭连接对象 connection
     */
    private void close(){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException("Close connection fail",e);
            }
        }
    }

}
